package com.blog4java.jdbc;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Properties;

import com.blog4java.jdbc.entities.Emp;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

/**
 * JDBC-Oracle的Statement接口例子（增删改查）
 * https://blog.csdn.net/qq_37805065/article/details/100673654
 * java操作Oracle数据库——ARRAY、TABLE类型批量入库
 * https://www.cnblogs.com/chenzhanxun/articles/7778163.html
 * oracle不支持的字符集orai18n.jar ZHS16GBK异常问题解决办法
 * https://www.jb51.net/database/315435sov.htm
 * @author suyuhao
 */
public class Example04 {
	public static void main(String[] args) {
		Example04 jdbc = new Example04();
		//查询操作
		/*		
 		ArrayList<Emp> result=jdbc.selectEmpAll();
		for(Emp e:result) {
			System.out.println(e.toString());
		}
		*/
		jdbc.insertSQL();//插入操作
		//jdbc.updateSQL();//更新操作
		//jdbc.deleteSQL();//删除操作
		//unwrap(Oracle的数组绑定功能)
		//jdbc.varrayInsertExample1();//插入操作
		//jdbc.varrayInsertExample2();//插入操作
		//jdbc.varrayQueryExample();
		//jdbc.oracleSpecificFeaturesExample();
	}
	
	/**
	 * 查询操作(通过DriverManager获取连接)
	 */
	public ArrayList<Emp> selectEmpAll() {
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		try {
			//1 载入JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2 定义连接URL
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";//<主机名(localhost)或IP>:1521(默认端口号):数据库名字
			//3 建立连接
			String username = "scott";// 用户
			String password = "5068403820";// 密码
			conn = DriverManager.getConnection(url, username, password);
			//4 创建Statement对象
			statement = conn.createStatement();
			//5 执行查询或更新
			String sql = "select * from emp";
			result = statement.executeQuery(sql);// select查询语句
			//6 结果处理
			ArrayList<Emp> empList = new ArrayList<>();
			while (result.next()) {
				//创建一个Emp类对象
				Emp emp = new Emp();
				//通过result的get()方法给emp对象属性赋值
				emp.setEmpno(result.getInt("empno"));
				emp.setEname(result.getString("ename"));
				emp.setJob(result.getString("job"));
				emp.setMgr(result.getInt("mgr"));
				emp.setHiredate(result.getDate("hiredate"));
				emp.setSal(result.getDouble("sal"));
				emp.setComm(result.getDouble("comm"));
				emp.setDeptno(result.getInt("deptno"));
				//添加
				empList.add(emp);
			}
			return empList;
		}catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 7 关闭连接（先打开后关闭）
			if (result != null) {
				try {
					result.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	
	/**
	 * 插入操作
	 */
	public void insertSQL() {
		Connection conn = null;
		PreparedStatement prepareStatement = null;
		try {
			//1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			//3 建立连接
			/**方式一*/	
 			String username = "scott";
			String password = "5068403820";
			conn = DriverManager.getConnection(url, username, password);
			/**方式二*/
			/**
			 * Oracle JDBC驱动程序提供了特定的属性来控制字符编码。例如，oracle.jdbc.V8NCharToRaw属性可以用来控制如何处理NCHAR和NVARCHAR2类型的字段。
			 * 对于大多数现代应用，你应该将此属性设置为false，以避免将Unicode字符转换为字节序列。
			 */					
			/*
 			Properties props = new Properties();
			props.setProperty("user", "scott");
			props.setProperty("password", "5068403820");
			props.setProperty("oracle.jdbc.V8NCharToRaw", "false"); //对于Oracle 10g及更高版本
			props.put("useUnicode", "true");
			//props.setProperty("characterEncoding", "UTF-8"); //可能在某些驱动中不适用	
			//props.setProperty("characterSet", "AL32UTF8");
			props.setProperty("characterSet", "ZHS16GBK");
			conn = DriverManager.getConnection(url, props);
			*/	
			/**方式三*/
			/** 对于Oracle JDBC驱动，更常见的方法是使用NLS_LANG参数来设置字符集:*/	
			/*
			Properties props = new Properties();
			props.setProperty("user", "scott");
			props.setProperty("password", "5068403820");
			props.put("useUnicode", "true");
			//props.setProperty("oracle.net.ORACLE_NLS_CHARSET", "AL32UTF8");//设置字符集为UTF-8
			props.setProperty("oracle.net.ORACLE_NLS_CHARSET", "ZHS16GBK");//设置字符集为	GBK							
			conn = DriverManager.getConnection(url, props);			
			*/		
			//4 创建Statement对象
			String sql = "insert into emp(empno,ename,job) values(?,?,?)";
			prepareStatement = conn.prepareStatement(sql);
			//5 执行SQL语句
			prepareStatement.setInt(1, 2192);
			prepareStatement.setString(2, "张三");
			prepareStatement.setString(3, "student");
			prepareStatement.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 6 关闭连接
			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 更新操作
	 */
	public void updateSQL() {
		Connection conn = null;
		PreparedStatement prepareStatement = null;
		try {
			//1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			//3 建立连接
			String username = "scott";
			String password = "5068403820";
			conn = DriverManager.getConnection(url, username, password);
			//4 创建Statement对象
			String sql = "update emp set ename=? where empno=?";
			prepareStatement = conn.prepareStatement(sql);
			//5 执行SQL语句		
			prepareStatement.setString(1, "李四");
			prepareStatement.setInt(2, 2192);
			int result=prepareStatement.executeUpdate();
			System.out.println(result);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 6 关闭连接
			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 删除操作
	 */
	public void deleteSQL() {
		Connection conn = null;
		PreparedStatement prepareStatement = null;
		try {
			//1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			//3 建立连接
			String username = "scott";
			String password = "5068403820";
			conn = DriverManager.getConnection(url, username, password);
			//4 创建Statement对象
			String sql = "delete from emp where empno=?";
			prepareStatement = conn.prepareStatement(sql);
			//5 执行SQL语句
			prepareStatement.setInt(1, 2192);
			int result = prepareStatement.executeUpdate();			
			System.out.println(result);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			//6 关闭连接
			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 使用unwrap方法来访问Oracle特有的功能
	 * 示例：使用Oracle的数组绑定功能
	 */
	//方式1:通过OracleConnection实现
	public void varrayInsertExample1() {
		Connection conn = null;
		Statement statement = null;
		try {
			//1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			//3 建立连接				
			String username = "scott";
			String password = "5068403820";
			conn = DriverManager.getConnection(url, username, password);			
			OracleConnection oracleConnection = conn.unwrap(OracleConnection.class);
			String namesTypeName="EMPLOYEE_NAMES";//VARRAY类型的名称，大写
			String idsTypeName="EMPLOYEE_IDS";//VARRAY类型的名称，大写
//			Array namesArray = oracleConnection.createOracleArray(namesTypeName, new Object[]{"张三","ALLEN","WARD",null});
//			Array idsArray = oracleConnection.createOracleArray(idsTypeName, new Object[] {1,2,3,null});
			ArrayDescriptor namesDesc = ArrayDescriptor.createDescriptor(namesTypeName, oracleConnection);
			Object[] names = new Object[]{"张三","ALLEN","WARD",null};
			ARRAY namesArray = new ARRAY(namesDesc, oracleConnection, names);
			ArrayDescriptor idsDesc = ArrayDescriptor.createDescriptor(idsTypeName, oracleConnection);
			Object[] ids = new Object[]{1,2,3,null};
			ARRAY idsArray = new ARRAY(idsDesc, oracleConnection, ids);
			//4 创建Statement对象
			String sql = "INSERT INTO t_employees(id,names,ids) VALUES (?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 2);
			pstmt.setObject(2, namesArray,OracleTypes.ARRAY);
			pstmt.setObject(3, idsArray, OracleTypes.ARRAY);
			//5 执行SQL语句
			pstmt.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 6 关闭连接
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	//方式2:通过OraclePreparedStatement实现
	public void varrayInsertExample2() {
		Connection conn = null;
		PreparedStatement prepareStatement = null;
		try {
			//1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");			
			//2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			//3 建立连接			
			String username = "scott";
			String password = "5068403820";
			conn = DriverManager.getConnection(url, username, password);				
			//4 创建Statement对象
			String sql = "INSERT INTO t_employees(id,names,ids) VALUES (?,?,?)";
			prepareStatement = conn.prepareStatement(sql);
			Class<?> clzz = Class.forName("oracle.jdbc.OraclePreparedStatement");
			if(prepareStatement.isWrapperFor(clzz)) {							
				OraclePreparedStatement oraclePstmt = prepareStatement.unwrap(OraclePreparedStatement.class);
				//5 创建数组数据和描述符
				String namesTypeName="EMPLOYEE_NAMES";//VARRAY类型的名称，大写
				String idsTypeName="EMPLOYEE_IDS";//VARRAY类型的名称，大写
				ArrayDescriptor namesDesc = ArrayDescriptor.createDescriptor(namesTypeName, conn);
				Object[] names = new Object[]{"张三","ALLEN","WARD",null};
				ARRAY namesArray = new ARRAY(namesDesc, conn, names);
				ArrayDescriptor idsDesc = ArrayDescriptor.createDescriptor(idsTypeName, conn);
				Object[] ids = new Object[]{1,2,3,null};
				ARRAY idsArray = new ARRAY(idsDesc, conn, ids);			
				//6 设置数组参数			
				oraclePstmt.setInt(1, 2);
				oraclePstmt.setARRAY(2, namesArray);
				oraclePstmt.setARRAY(3, idsArray);
				oraclePstmt.execute();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 6 关闭连接
			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	//查询varray
	public void varrayQueryExample() {
		Connection conn = null;
		Statement statement = null;
		try {
			//1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			//3 建立连接
			String username = "scott";
			String password = "5068403820";
			conn = DriverManager.getConnection(url, username, password);
			//4 创建Statement对象
			String sql = "SELECT id,names,ids FROM t_employees WHERE id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 2);
			//5 执行SQL语句
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Array namesArray = rs.getArray("names");
				Object[] names =(Object[])namesArray.getArray();
				System.out.println("names:"+Arrays.toString(names));
				Array idsArray = rs.getArray("ids");
				Object[] ids =(Object[])idsArray.getArray();
				System.out.println("ids:"+Arrays.toString(ids));				
			}		
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 6 关闭连接
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	//使用unwrap方法获取Oracle特有的连接属性
	public void oracleSpecificFeaturesExample(){
		try {
			OracleDataSource dataSource = new OracleDataSource();
			dataSource.setURL("jdbc:oracle:thin:@127.0.0.1:1521:orcl");
			dataSource.setUser("scott");
			dataSource.setPassword("5068403820");
			//通过DataSource对象获取Connection对象
			Connection conn = dataSource.getConnection();
/*			Statement stmt = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE, 
					ResultSet.CONCUR_UPDATABLE, 
					ResultSet.HOLD_CURSORS_OVER_COMMIT);*/
			Class<?> clzz = Class.forName("oracle.jdbc.OracleConnection");	
			if(conn.isWrapperFor(clzz)) {
				//使用unwrap方法获取OracleConnection实例
				OracleConnection oracleConn = (OracleConnection)conn.unwrap(clzz);
				//设置额外的语句超时时间
			    oracleConn.setStatementCacheSize(100); // 设置语句缓存大小
			    System.out.println("Statement Cache Size set to: " + oracleConn.getStatementCacheSize());
			    //获取额外的连接属性
			    Properties props = oracleConn.getClientInfo();
			    System.out.println("Client Info: " + props);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
